set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=1000;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=1000; --  每天生成 60 个分区

insert overwrite table jms_dm.dm_center_transfer_summary_dt partition(dt)
select
   scan_time
  ,scan_site_code
  ,scan_site_name
  ,provider_id
  ,provider_desc
  ,city_id
  ,city_desc
  ,regional_id
  ,regional_desc
  ,agent_code
  ,agent_name
  ,order_source_code
  ,order_source_name
  ,max(oper_shift) as oper_shift           --操作班次
  ,sum(arrival_cnt) as arrival_cnt         --到件量
  ,sum(send_cnt) as send_cnt            --发件量
  ,0 as arrival_pack_cnt    --到包量
  ,0 as send_pack_cnt       --发包量
  ,sum(enterport_cnt) as enterport_cnt       --进港件量汇总
  ,sum(same_city_cnt) as same_city_cnt       --同城件量汇总
  ,sum(transfer_cnt ) as transfer_cnt        --中转件量汇总
  ,sum(leaveport_cnt) as leaveport_cnt       --出港件量汇总
  ,0 as enterport_pack_cnt  --进港包量汇总
  ,0 as same_city_pack_cnt  --同城包量汇总
  ,0 as transfer_pack_cnt   --中转包量汇总
  ,0 as leaveport_pack_cnt  --出港包量汇总
  ,sum(enterport_weight_tot) as enterport_weight_tot--进港重量汇总
  ,sum(same_city_weight_tot) as same_city_weight_tot--同城重量汇总
  ,sum(transfer_weight_tot ) as transfer_weight_tot --中转重量汇总
  ,sum(leaveport_weight_tot) as leaveport_weight_tot--出港重量汇总
  ,sum(trans_cnt           ) as trans_cnt           --转运货量/件
  ,0 as trans_pack_cnt      --转运货量/包
  ,sum(oper_cnt) as oper_cnt            --操作件量
  ,0 as oper_pack_cnt       --操作包量
  ,sum(oper_weight_tot) as oper_weight_tot     --操作重量汇总
  ,sum(other_cnt) as other_cnt           --其他件量
  ,0 as other_pack_cnt      --其他包量
  ,sum(other_weight_tot) as other_weight_tot    --其他重量
  ,max(sys_time) as sys_time
  ,max(effect_start_time) as effect_start_time
  ,max(effect_end_time) as effect_end_time
  ,max(shift_start_time) as shift_start_time
  ,max(shift_end_time) as shift_end_time
  ,'自然日' as sum_flag
  ,dt
from jms_dm.dm_center_transfer_summary_new_dt
where dt>=date_sub('{{ execution_date | cst_ds }}',2) and dt<='{{ execution_date | cst_ds }}'
  and scan_time>=date_sub('{{ execution_date | cst_ds }}',2) and scan_time<='{{ execution_date | cst_ds }}'
  and sum_flag_code='zrr'
group by
 scan_time
,dt
,scan_site_code
,scan_site_name
,provider_id
,provider_desc
,city_id
,city_desc
,regional_id
,regional_desc
,agent_code
,agent_name
,order_source_code
,order_source_name
distribute by 1
;